package com.hncj.yqfk.dao;

import com.hncj.yqfk.entity.CRDJ;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

@Repository
public interface CRDJ_Dao extends JpaRepository<CRDJ,String> {

    /**
     * 查询当天目前楼宇停留人数排名前十
     * @param day
     * @return
     */
    @Query(value = "SELECT LYMC,COUNT(CASE JCBZ WHEN '1' THEN 1 ELSE NULL END ) AS NUM\n" +
            "FROM V_XGXT_BZB_YQ_WGH_CRDJ WHERE to_char(CRSJ,'YYYY-MM-DD') LIKE :day AND USERNAME != '01' GROUP BY LYMC",nativeQuery = true)
    List<Map<String,Integer>> GetOrder(@Param("day") String day);

    /**
     * 计算当前建筑的容纳度(LYMC后加%)
     * @param LYMC 楼宇名称
     * @param day
     * @return
     */
    @Query(value = "SELECT ((COUNT(CASE JCBZ WHEN '1' THEN 1 ELSE NULL END )-COUNT(CASE JCBZ WHEN '2' THEN 1 ELSE NULL END ))/LYRL)*100 AS RND FROM V_XGXT_BZB_YQ_WGH_CRDJ WHERE LYMC LIKE :LYMC AND to_char(CRSJ,'YYYY-MM-DD') LIKE :day GROUP BY LYRL", nativeQuery = true)
    Double GetRndByLybh(@Param("LYMC") String LYMC,@Param("day") String day);

    /**
     * 计算当前建筑小时内的人流量(LYMC后加%)
     * @param LYMC 楼宇名称
     * @param start_time
     * @return
     */
    @Query(value = "SELECT COUNT(CASE JCBZ WHEN '1' THEN 1 ELSE NULL END ) AS RND FROM V_XGXT_BZB_YQ_WGH_CRDJ WHERE LYMC LIKE :LYMC AND to_char(CRSJ,'YYYY-MM-DD HH') LIKE :start_time", nativeQuery = true)
    Integer GetHourRndByLybh(@Param("LYMC") String LYMC,@Param("start_time") String start_time);

    /**
     * 根据日期和建筑编号的到当天每小时的人流情况(LYMC后加%)
     * @param LYMC 楼宇名称
     * @param stime
     * @param etime
     * @param stype 用户身份（学生/教师/其他）
     * @return
     */
    @Query(value = "SELECT to_char(CRSJ,'mm-dd:HH24') AS HOUR,COUNT(CASE JCBZ WHEN '1' THEN 1 ELSE NULL END ) AS HOUR_JNUM FROM V_XGXT_BZB_YQ_WGH_CRDJ WHERE LYMC LIKE :LYMC AND USERTYPE LIKE :stype AND CRSJ BETWEEN to_date(:stime,'yyyy-MM-dd hh24:mi:ss') AND to_date(:etime,'yyyy-MM-dd hh24:mi:ss') GROUP BY to_char(CRSJ,'mm-dd:HH24') ORDER BY to_char(CRSJ,'mm-dd:HH24')",nativeQuery = true)
    List<Map<String,Object>> GetByDay(@Param("LYMC") String LYMC,@Param("stime") String stime,@Param("etime") String etime,@Param("stype") String stype);

    // 根据用户名和时间段获取全部
    @Query(value = "SELECT * FROM V_XGXT_BZB_YQ_WGH_CRDJ WHERE USERNAME=:USERNAME AND CRSJ BETWEEN to_date(:stime,'yyyy-MM-dd hh24:mi:ss') AND to_date(:etime,'yyyy-MM-dd hh24:mi:ss') ORDER BY CRSJ ",nativeQuery = true)
    List<CRDJ> GetByDayAndUsername(@Param("USERNAME") String USERNAME, @Param("stime") String stime, @Param("etime") String etime);

}